﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;

//@author A0085749L
namespace EZXPNS
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        private void MainForm_Load(object sender, EventArgs e)
        {
            //Load _incomeList
            StreamReader inputfile;
            inputfile = File.OpenText("../../IncomeList.txt");

            while (inputfile.EndOfStream == false)
            {
                string tempName = inputfile.ReadLine();
                string tempAmount = inputfile.ReadLine();
                string tempCategory = inputfile.ReadLine();
                string tempDate = inputfile.ReadLine();
                string tempPayment = inputfile.ReadLine();

                Item tempItem = new Item();
                tempItem.Name = tempName;
                tempItem.Amount = decimal.Parse(tempAmount);
                tempItem.Category = tempCategory;
                tempItem.Date = tempDate;
                tempItem.Payment = tempPayment;
                EzXpns._incomeList.Add(tempItem);
            }
            inputfile.Close();

            //Load _expenseList
            StreamReader inputfile2;
            inputfile2 = File.OpenText("../../ExpenseList.txt");

            while (inputfile2.EndOfStream == false)
            {
                string tempName = inputfile2.ReadLine();
                string tempAmount = inputfile2.ReadLine();
                string tempCategory = inputfile2.ReadLine();
                string tempDate = inputfile2.ReadLine();
                string tempPayment = inputfile2.ReadLine();

                Item tempItem = new Item();
                tempItem.Name = tempName;
                tempItem.Amount = decimal.Parse(tempAmount);
                tempItem.Category = tempCategory;
                tempItem.Date = tempDate;
                tempItem.Payment = tempPayment;
                EzXpns._expenseList.Add(tempItem);
            }
            inputfile2.Close();
            
            //Load Payment Account Balance
            StreamReader inputfile3;
            inputfile3 = File.OpenText("../../Payment.txt");

            while (inputfile3.EndOfStream == false)
            {
                Payment.Credit = decimal.Parse(inputfile3.ReadLine());
                Payment.Debit = decimal.Parse(inputfile3.ReadLine());
                Payment.Cash = decimal.Parse(inputfile3.ReadLine());
                Payment.Ezlink = decimal.Parse(inputfile3.ReadLine());
                Payment.Others = decimal.Parse(inputfile3.ReadLine());
            }
            inputfile3.Close();

            //Load alert information and display monthly expense, income of the current month and goal saving
            AlertLabel.Text = EzXpns.Alert();

            DateTime dtNow = DateTime.Now, temp;
            int tempMonth, tempYear, curMonth = dtNow.Month, curYear = dtNow.Year;

            decimal totalIncome = 0m, curMonthlyIncome=0m;
            for (int i = 0; i < EzXpns._incomeList.Count; i++)
            {
                totalIncome += EzXpns._incomeList[i].Amount;
                temp = Convert.ToDateTime(EzXpns._incomeList[i].Date);
                tempMonth = temp.Month;
                tempYear = temp.Year;
                if (tempMonth == curMonth && tempYear == curYear)
                    curMonthlyIncome += Convert.ToDecimal(EzXpns._incomeList[i].Amount);

            }
            currentMonthlyIncome.Text = "S$"+ curMonthlyIncome.ToString();

            decimal totalExpense = 0m, curMonthlyExp=0m;
            for (int i = 0; i < EzXpns._expenseList.Count; i++)
            {
                totalExpense += EzXpns._expenseList[i].Amount;
                temp = Convert.ToDateTime(EzXpns._expenseList[i].Date);
                tempMonth = temp.Month;
                tempYear = temp.Year;
                if (tempMonth == curMonth && tempYear == curYear)
                    curMonthlyExp += Convert.ToDecimal(EzXpns._expenseList[i].Amount);
            }
            currentMonthlyExpense.Text = "S$"+curMonthlyExp.ToString();

            StreamReader inputfile4;
            inputfile4 = File.OpenText("../../GoalSaving.txt");
            decimal goal = decimal.Parse(inputfile4.ReadLine());
            goalSaving.Text = "S$"+goal.ToString();
            inputfile4.Close();

            if (totalExpense > totalIncome)
                AlertLabel.ForeColor = Color.Red;
            else if ((totalIncome - totalExpense) < goal * 0.2m)
                AlertLabel.ForeColor = Color.Orange;
            else if ((totalIncome - totalExpense) < goal * 0.5m)
                AlertLabel.ForeColor = Color.YellowGreen;
            else if ((totalIncome - totalExpense) < goal * 0.8m)
                AlertLabel.ForeColor = Color.Green;
            else if ((totalIncome - totalExpense) < goal)
                AlertLabel.ForeColor = Color.Blue;
            else
                AlertLabel.ForeColor = Color.Purple;

            //progressbar
            if (totalExpense > totalIncome)
            {
                tenpercent.BackColor = Color.Red;
                twentypercent.BackColor = Color.Red;
                thirtypercent.BackColor = Color.Red;
                fortypercent.BackColor = Color.Red;
                fiftypercent.BackColor = Color.Red;
                sixtypercent.BackColor = Color.Red;
                seventypercent.BackColor = Color.Red;
                eightypercent.BackColor = Color.Red;
                ninetypercent.BackColor = Color.Red;
                hundredpercent.BackColor = Color.Red;
            }
            else if (!(totalExpense > totalIncome))
            {
                if ((totalIncome - totalExpense) >= goal)
                    hundredpercent.BackColor = Color.Green;
                if ((totalIncome - totalExpense) > goal * 0.9m)
                    ninetypercent.BackColor = Color.Green;
                if ((totalIncome - totalExpense) > goal * 0.8m)
                    eightypercent.BackColor = Color.Green;
                if ((totalIncome - totalExpense) > goal * 0.7m)
                    seventypercent.BackColor = Color.Green;
                if ((totalIncome - totalExpense) > goal * 0.6m)
                    sixtypercent.BackColor = Color.Green;
                if ((totalIncome - totalExpense) > goal * 0.5m)
                    fiftypercent.BackColor = Color.Green;
                if ((totalIncome - totalExpense) > goal * 0.4m)
                    fortypercent.BackColor = Color.Green;
                if ((totalIncome - totalExpense) > goal * 0.3m)
                    thirtypercent.BackColor = Color.Green;
                if ((totalIncome - totalExpense) > goal * 0.2m)
                    twentypercent.BackColor = Color.Green;
                if ((totalIncome - totalExpense) > goal * 0.1m)
                    tenpercent.BackColor = Color.Green;
            }
            else
            {
                tenpercent.BackColor = SystemColors.Control;
                twentypercent.BackColor = SystemColors.Control; 
                thirtypercent.BackColor = SystemColors.Control;
                fortypercent.BackColor = SystemColors.Control;
                fiftypercent.BackColor = SystemColors.Control;
                sixtypercent.BackColor = SystemColors.Control;
                seventypercent.BackColor = SystemColors.Control;
                eightypercent.BackColor = SystemColors.Control;
                ninetypercent.BackColor = SystemColors.Control;
                hundredpercent.BackColor = SystemColors.Control;
            }
        }
        
        private void IncomeList_Click(object sender, EventArgs e)
        {
            IncomeForm _incomeForm = new IncomeForm(this);
            _incomeForm.ShowDialog();
        }

        private void ExpenseList_Click(object sender, EventArgs e)
        {
            ExpenseForm _expenseForm = new ExpenseForm(this);
            _expenseForm.ShowDialog();
        }

        private void PaymentAccount_Click(object sender, EventArgs e)
        {
            PaymentAccountForm _paymentForm = new PaymentAccountForm();
            _paymentForm.ShowDialog();
        }

        private void Search_Click(object sender, EventArgs e)
        {
            SearchForm _searchForm = new SearchForm();
            _searchForm.ShowDialog();
        }

        private void SetGoalSaving_Click(object sender, EventArgs e)
        {
            SetGoalSavingForm _setGoalSavingForm = new SetGoalSavingForm(this);
            _setGoalSavingForm.ShowDialog();
        }

        private void Report_Click(object sender, EventArgs e)
        {
            ReportForm _reportForm = new ReportForm();
            _reportForm.ShowDialog();
        }

        //@author A0090998H
        private void Alerts_Click(object sender, EventArgs e)
        {
            decimal totalIncome = 0m;
            for (int i = 0; i < EzXpns._incomeList.Count; i++)
            {
                totalIncome += EzXpns._incomeList[i].Amount;
            }

            decimal totalExpense = 0m;
            for (int i = 0; i < EzXpns._expenseList.Count; i++)
            {
                totalExpense += EzXpns._expenseList[i].Amount;
            }
            
            StreamReader inputfile;
            inputfile = File.OpenText("../../GoalSaving.txt");
            decimal goal = decimal.Parse(inputfile.ReadLine());
            inputfile.Close();

            if (totalExpense > totalIncome)
                MessageBox.Show("You are spending too much!");
            else if ((totalIncome - totalExpense) < goal * 0.1m)
                MessageBox.Show("You are below 10% to the goal!");
            else if((totalIncome - totalExpense) < goal *0.2m)
                MessageBox.Show("You are around 20% to the goal!");
            else if ((totalIncome - totalExpense) < goal * 0.3m)
                MessageBox.Show("You are around 30% to the goal!");
            else if ((totalIncome - totalExpense) < goal * 0.4m)
                MessageBox.Show("You are around 40% to the goal!");
            else if ((totalIncome - totalExpense) < goal * 0.5m)
                MessageBox.Show("You are halfway to the goal!");
            else if ((totalIncome - totalExpense) < goal * 0.6m)
                MessageBox.Show("You are around 60% to the goal!");
            else if ((totalIncome - totalExpense) < goal * 0.7m)
                MessageBox.Show("You are around 70% to the goal!");
            else if ((totalIncome - totalExpense) < goal * 0.8m)
                MessageBox.Show("You are around 80% to the goal!");
            else if ((totalIncome - totalExpense) < goal * 0.9m)
                MessageBox.Show("You are around 90% to the goal!");
            else if ((totalIncome - totalExpense) < goal )
                MessageBox.Show("You are almost there!");
            else
                MessageBox.Show("Congratulations! You have reached the goal!");
        }

        private void PowerSearch_Click(object sender, EventArgs e)
        {
            SearchPower _powerSearch = new SearchPower();
            _powerSearch.ShowDialog();
        }
        
        private void Import_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel Workbook|*.xlsx";
            if (ofd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                try
                {
                    using (ExcelPackage p = new ExcelPackage())
                    {
                        using (FileStream stream = new FileStream(ofd.FileName, FileMode.Open))
                        {
                            p.Load(stream);

                            ExcelWorksheet ws = p.Workbook.Worksheets[1];

                            //Import Income
                            for (int i = 3; ws.Cells[i, 1].Value != null; i++)
                            {
                                //Change Payment
                                switch (ws.Cells[i, 5].Value.ToString())
                                {
                                    case "Credit":
                                        Payment.Credit += decimal.Parse(ws.Cells[i, 2].Value.ToString());
                                        break;
                                    case "Debit":
                                        Payment.Debit += decimal.Parse(ws.Cells[i, 2].Value.ToString());
                                        break;
                                    case "Cash":
                                        Payment.Cash += decimal.Parse(ws.Cells[i, 2].Value.ToString());
                                        break;
                                    case "Ezlink":
                                        Payment.Ezlink += decimal.Parse(ws.Cells[i, 2].Value.ToString());
                                        break;
                                    default:
                                        Payment.Others += decimal.Parse(ws.Cells[i, 2].Value.ToString());
                                        break;
                                }

                                Payment.UpdatePayment();

                                //Add to _incomeList
                                Item newItem = new Item();
                                newItem.Name = ws.Cells[i, 1].Value.ToString();
                                newItem.Amount = decimal.Parse(ws.Cells[i, 2].Value.ToString());
                                newItem.Category = ws.Cells[i, 3].Value.ToString();
                                newItem.Date = ws.Cells[i, 4].Value.ToString();
                                newItem.Payment = ws.Cells[i, 5].Value.ToString();

                                EzXpns._incomeList.Add(newItem);
                                EzXpns.SaveIncome();
                            }

                            //Import Expense
                            for (int i = 3; ws.Cells[i, 7].Value != null; i++)
                            {
                                //Change Payment
                                switch (ws.Cells[i, 11].Value.ToString())
                                {
                                    case "Credit":
                                        Payment.Credit -= decimal.Parse(ws.Cells[i, 8].Value.ToString());
                                        break;
                                    case "Debit":
                                        Payment.Debit -= decimal.Parse(ws.Cells[i, 8].Value.ToString());
                                        break;
                                    case "Cash":
                                        Payment.Cash -= decimal.Parse(ws.Cells[i, 8].Value.ToString());
                                        break;
                                    case "Ezlink":
                                        Payment.Ezlink -= decimal.Parse(ws.Cells[i, 8].Value.ToString());
                                        break;
                                    default:
                                        Payment.Others -= decimal.Parse(ws.Cells[i, 8].Value.ToString());
                                        break;
                                }

                                Payment.UpdatePayment();

                                //Add to _expenseList
                                Item newItem = new Item();
                                newItem.Name = ws.Cells[i, 7].Value.ToString();
                                newItem.Amount = decimal.Parse(ws.Cells[i, 8].Value.ToString());
                                newItem.Category = ws.Cells[i, 9].Value.ToString();
                                newItem.Date = ws.Cells[i, 10].Value.ToString();
                                newItem.Payment = ws.Cells[i, 11].Value.ToString();

                                EzXpns._expenseList.Add(newItem);
                                EzXpns.SaveExpense();
                            }

                            MessageBox.Show("Import from excel done!");
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        //@author A0088027B
        private void Export_Click(object sender, EventArgs e)
        {
            //Engage the user to select a saving location
            SaveFileDialog sfd = new SaveFileDialog();
            // Set the file name and get the output directory
            var fileName = "ExportToExcel" + DateTime.Now.ToString("yyyy-MM-dd--hh-mm-ss") + ".xlsx";
            sfd.Filter = "Excel Workbook|*.xlsx";
            sfd.FileName = fileName;

            if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                try
                {
                    // Create the file using the FileInfo object
                    var file = new FileInfo(sfd.FileName);
                    using (ExcelPackage xlPackage = new ExcelPackage(file))
                    {
                        ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("EzXpns" + DateTime.Now.ToShortDateString());

                        // Add some formatting to the worksheet
                        worksheet.TabColor = Color.Blue;
                        worksheet.DefaultRowHeight = 12;
                        worksheet.HeaderFooter.FirstFooter.LeftAlignedText = string.Format("Generated: {0}", DateTime.Now.ToShortDateString());
                        worksheet.Row(1).Height = 20;
                        worksheet.Row(2).Height = 18;

                        // write income items
                        //header
                        worksheet.Cells[1, 1].Value = "Income";
                        worksheet.Cells[2, 1].Value = "Name";
                        worksheet.Cells[2, 2].Value = "Amount";
                        worksheet.Cells[2, 3].Value = "Category";
                        worksheet.Cells[2, 4].Value = "Date";
                        worksheet.Cells[2, 5].Value = "Payment";

                        //format the first row of the header, but only the first column;
                        using (var range = worksheet.Cells[1, 1, 1, 1])
                        {
                            range.Style.Font.Bold = true;
                            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            range.Style.Fill.BackgroundColor.SetColor(Color.Black);
                            range.Style.Font.Color.SetColor(Color.WhiteSmoke);
                            range.Style.ShrinkToFit = false;
                        }
                        // format the second row of the header, but only the first five columns;
                        using (var range = worksheet.Cells[2, 1, 2, 5])
                        {
                            range.Style.Font.Bold = true;
                            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            range.Style.Fill.BackgroundColor.SetColor(Color.Gray);
                            range.Style.Font.Color.SetColor(Color.Black);
                            range.Style.ShrinkToFit = false;
                        }

                        StreamReader inputfile;
                        inputfile = File.OpenText("../../IncomeList.txt");

                        int i = 3;
                        while (inputfile.EndOfStream == false)
                        {
                            string tempName = inputfile.ReadLine();
                            string tempAmount = inputfile.ReadLine();
                            string tempCategory = inputfile.ReadLine();
                            string tempDate = inputfile.ReadLine();
                            string tempPayment = inputfile.ReadLine();

                            worksheet.Cells[i, 1].Value = tempName;
                            worksheet.Cells[i, 2].Value = tempAmount;
                            worksheet.Cells[i, 3].Value = tempCategory;
                            worksheet.Cells[i, 4].Value = tempDate;
                            worksheet.Cells[i, 5].Value = tempPayment;

                            i++;
                        }
                        inputfile.Close();

                        //format the income items;
                        using (var range = worksheet.Cells[3, 1, i - 1, 5])
                        {
                            range.Style.Font.Bold = false;
                            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            range.Style.Fill.BackgroundColor.SetColor(Color.LightSteelBlue);
                            range.Style.Font.Color.SetColor(Color.Black);
                            range.Style.ShrinkToFit = false;
                        }

                        //Calculate total
                        worksheet.Cells[i + 1, 1].Value = "Total income";
                        decimal totalIncome = 0m;
                        for (int m = 0; m < EzXpns._incomeList.Count; m++)
                        {
                            totalIncome += EzXpns._incomeList[m].Amount;
                        }
                        worksheet.Cells[i + 1, 2].Value = totalIncome.ToString();
                        //format these two cells
                        using (var range = worksheet.Cells[i + 1, 1, i + 1, 2])
                        {
                            range.Style.Font.Bold = false;
                            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            range.Style.Fill.BackgroundColor.SetColor(Color.Gray);
                            range.Style.Font.Color.SetColor(Color.WhiteSmoke);
                            range.Style.ShrinkToFit = false;
                        }

                        //write expense 
                        StreamReader inputfile2;
                        inputfile2 = File.OpenText("../../ExpenseList.txt");

                        worksheet.Cells[1, 7].Value = "Expense";
                        worksheet.Cells[2, 7].Value = "Name";
                        worksheet.Cells[2, 8].Value = "Amount";
                        worksheet.Cells[2, 9].Value = "Category";
                        worksheet.Cells[2, 10].Value = "Date";
                        worksheet.Cells[2, 11].Value = "Payment";

                        //format the first row of the header, but only the first column;
                        using (var range = worksheet.Cells[1, 7, 1, 7])
                        {
                            range.Style.Font.Bold = true;
                            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            range.Style.Fill.BackgroundColor.SetColor(Color.Black);
                            range.Style.Font.Color.SetColor(Color.WhiteSmoke);
                            range.Style.ShrinkToFit = false;
                        }
                        // format the second row of the header, but only the first five columns;
                        using (var range = worksheet.Cells[2, 7, 2, 11])
                        {
                            range.Style.Font.Bold = true;
                            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            range.Style.Fill.BackgroundColor.SetColor(Color.Gray);
                            range.Style.Font.Color.SetColor(Color.Black);
                            range.Style.ShrinkToFit = false;
                        }
                        //write expense items
                        i = 3;
                        while (inputfile2.EndOfStream == false)
                        {
                            string tempName = inputfile2.ReadLine();
                            string tempAmount = inputfile2.ReadLine();
                            string tempCategory = inputfile2.ReadLine();
                            string tempDate = inputfile2.ReadLine();
                            string tempPayment = inputfile2.ReadLine();

                            worksheet.Cells[i, 7].Value = tempName;
                            worksheet.Cells[i, 8].Value = tempAmount;
                            worksheet.Cells[i, 9].Value = tempCategory;
                            worksheet.Cells[i, 10].Value = tempDate;
                            worksheet.Cells[i, 11].Value = tempPayment;

                            i++;
                        }
                        inputfile2.Close();

                        //format the expense items;
                        using (var range = worksheet.Cells[3, 7, i - 1, 11])
                        {
                            range.Style.Font.Bold = false;
                            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            range.Style.Fill.BackgroundColor.SetColor(Color.LightSteelBlue);
                            range.Style.Font.Color.SetColor(Color.Black);
                            range.Style.ShrinkToFit = false;
                        }

                        //Calculate total expense
                        worksheet.Cells[i + 1, 7].Value = "Total expense";
                        decimal totalExpense = 0m;
                        for (int m = 0; m < EzXpns._expenseList.Count; m++)
                        {
                            totalExpense += EzXpns._expenseList[m].Amount;
                        }
                        worksheet.Cells[i + 1, 8].Value = totalExpense.ToString();
                        //format these two cells
                        using (var range = worksheet.Cells[i + 1, 7, i + 1, 8])
                        {
                            range.Style.Font.Bold = false;
                            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            range.Style.Fill.BackgroundColor.SetColor(Color.Gray);
                            range.Style.Font.Color.SetColor(Color.WhiteSmoke);
                            range.Style.ShrinkToFit = false;
                        }

                        //Write Payment Account Header
                        worksheet.Cells[1, 13].Value = "Payment";
                        worksheet.Cells[1, 13].Style.Font.Bold = true;
                        worksheet.Cells[1, 13].Style.Fill.PatternType = ExcelFillStyle.Solid;
                        worksheet.Cells[1, 13].Style.Fill.BackgroundColor.SetColor(Color.Black);
                        worksheet.Cells[1, 13].Style.Font.Color.SetColor(Color.WhiteSmoke);
                        worksheet.Cells[1, 13].Style.ShrinkToFit = false;

                        worksheet.Cells[2, 13].Value = "Account";
                        worksheet.Cells[2, 14].Value = "Amount";
                        //format these two cells
                        using (var range = worksheet.Cells[2, 13, 2, 14])
                        {
                            range.Style.Font.Bold = false;
                            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            range.Style.Fill.BackgroundColor.SetColor(Color.Gray);
                            range.Style.Font.Color.SetColor(Color.WhiteSmoke);
                            range.Style.ShrinkToFit = false;
                        }
                        //payment account
                        worksheet.Cells[3, 13].Value = "Credit";
                        worksheet.Cells[4, 13].Value = "Debit";
                        worksheet.Cells[5, 13].Value = "Cash";
                        worksheet.Cells[6, 13].Value = "Ezlink";
                        worksheet.Cells[7, 13].Value = "Others";
                        //payment amount
                        worksheet.Cells[3, 14].Value = Payment.Credit.ToString();
                        worksheet.Cells[4, 14].Value = Payment.Debit.ToString();
                        worksheet.Cells[5, 14].Value = Payment.Cash.ToString();
                        worksheet.Cells[6, 14].Value = Payment.Ezlink.ToString();
                        worksheet.Cells[7, 14].Value = Payment.Others.ToString();

                        //format the payment account
                        using (var range = worksheet.Cells[3, 13, 7, 13])
                        {
                            range.Style.Font.Bold = false;
                            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            range.Style.Fill.BackgroundColor.SetColor(Color.Gray);
                            range.Style.Font.Color.SetColor(Color.WhiteSmoke);
                            range.Style.ShrinkToFit = false;
                        }
                        //format the payment amount;
                        using (var range = worksheet.Cells[3, 14, 7, 14])
                        {
                            range.Style.Font.Bold = false;
                            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            range.Style.Fill.BackgroundColor.SetColor(Color.LightSteelBlue);
                            range.Style.Font.Color.SetColor(Color.Black);
                            range.Style.ShrinkToFit = false;
                        }

                        //Write and Format GoalSaving Header
                        worksheet.Cells[1, 16].Value = "Goal Saving";
                        worksheet.Cells[1, 16].Style.Font.Bold = true;
                        worksheet.Cells[1, 16].Style.Fill.PatternType = ExcelFillStyle.Solid;
                        worksheet.Cells[1, 16].Style.Fill.BackgroundColor.SetColor(Color.Black);
                        worksheet.Cells[1, 16].Style.Font.Color.SetColor(Color.WhiteSmoke);
                        worksheet.Cells[1, 16].Style.ShrinkToFit = false;

                        //Write GoalSaving amount
                        StreamReader inputfile4;
                        inputfile4 = File.OpenText("../../GoalSaving.txt");
                        string xamount = inputfile4.ReadLine();
                        inputfile4.Close();
                        worksheet.Cells[2, 16].Value = xamount;
                        //Format GoalSaving amount
                        worksheet.Cells[2, 16].Style.Font.Bold = false;
                        worksheet.Cells[2, 16].Style.Fill.PatternType = ExcelFillStyle.Solid;
                        worksheet.Cells[2, 16].Style.Fill.BackgroundColor.SetColor(Color.LightSteelBlue);
                        worksheet.Cells[2, 16].Style.Font.Color.SetColor(Color.Black);
                        worksheet.Cells[2, 16].Style.ShrinkToFit = false;

                        // Fit the columns according to its content
                        for (int k = 1; k <= 16; k++)
                        {
                            worksheet.Column(k).AutoFit();
                        }
                        xlPackage.Save();
                        MessageBox.Show("Export to excel done!");
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }
    }
}
